
package edu.weber.bookstore.database;

import edu.weber.bookstore.util.DatabaseConnection;
import edu.weber.bookstore.util.DatabaseModel;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 * 
 * 
 */
public class UserCartBook extends DatabaseModel
{
	/**
	 * table tied to the database
	 */
	protected static String TABLE_NAME = "users_carts_books";
	private int _userCartId;
	private int _bookId;
	private int _qty;
	private Book _book;
	
	/**
	 * Constructor for a UserCartBook
	 * @param userCartId a primary key of a UserCart to which we belong to.
	 * @param qty a quantity of the number of books.
	 * @param bookId a primary key of the book that corresponds to us in a UserCartBook.
	 */
	public UserCartBook(int userCartId, int qty, int bookId)
	{
		this(0, userCartId, qty, bookId, null);
	}
	
	/**
	 * Constructor for a UserCartBook
	 * @param userCartId a primary key of a UserCart to which we belong to.
	 * @param qty a quantity of the number of books.
	 * @param bookId a primary key of the book that corresponds to us in a UserCartBook.
	 * @param book the book object to which the book_id is for
	 */
	public UserCartBook(int userCartId, int qty, int bookId, Book book)
	{
		this(0, userCartId, qty, bookId, book);
	}
	
	/**
	 * Constructor for a UserCartBook
	 * @param id primary key for a UserCartBook
	 * @param userCartId a primary key of a UserCart to which we belong to.
	 * @param qty a quantity of the number of books.
	 * @param bookId a primary key of the book that corresponds to us in a UserCartBook.
	 * @param book the book object to which the book_id is for
	 */
	public UserCartBook(int id, int userCartId, int qty, int bookId, Book book)
	{
		super(id);
		_userCartId = userCartId;
		_bookId = bookId;
		_book = book;
		_qty = qty;
	}
	
	/**
	 * returns all UserCartBooks associated with a users_cart_id
	 * @param users_cart_id the primary key for the UserCart to which we belong to
	 * @return a list of UserCartBooks in the UserCart
	 * @throws SQLException
	 */
	public static ArrayList<UserCartBook> select(int users_cart_id) throws SQLException
	{
		ArrayList<UserCartBook> ucbList = new ArrayList<UserCartBook>();
		PreparedStatement pm = DatabaseConnection.getConnection().prepareStatement("SELECT * FROM ".concat(TABLE_NAME).concat(" as uc JOIN ").concat(Book.TABLE_NAME).concat(" AS b ON b.id = uc.book_id WHERE users_cart_id = ?;"));
		pm.setInt(1, users_cart_id);
		
		ResultSet rs = pm.executeQuery();
		
		int id_in = rs.findColumn("id");
		int users_cart_id_in = rs.findColumn("users_cart_id");
		int book_id_in = rs.findColumn("book_id");
		int qty_in = rs.findColumn("qty");
		while(rs.next())
		{
			int book_id = rs.getInt(book_id_in);
			ucbList.add(new UserCartBook(rs.getInt(id_in), rs.getInt(users_cart_id_in), rs.getInt(qty_in), book_id, Book.find(book_id)));
		}
		
		return ucbList;
	}
	
	/**
	 * Returns a UserCartBook by the primary key
	 * @param id a primary key for a desired UserCartBook.
	 * @return a UserCartBook object whose primary key is the input.
	 * @throws SQLException 
	 */
	public static UserCartBook find(int id) throws SQLException
	{
		PreparedStatement pm = DatabaseConnection.getConnection().prepareStatement("SELECT * FROM ".concat(TABLE_NAME).concat(" as uc JOIN ").concat(Book.TABLE_NAME).concat(" AS b ON b.id = uc.book_id WHERE uc.id = ? LIMIT 1;"));
		pm.setInt(1, id);
		
		ResultSet rs = pm.executeQuery();
		
		int id_in = rs.findColumn("id");
		int users_cart_id_in = rs.findColumn("users_cart_id");
		int book_id_in = rs.findColumn("book_id");
		int qty_in = rs.findColumn("qty");
		if(rs.next())
		{
			int book_id = rs.getInt(book_id_in);
			return new UserCartBook(rs.getInt(id_in), rs.getInt(users_cart_id_in), rs.getInt(qty_in), book_id, Book.find(book_id));
		}
		
		return null;
	}
	
	@Override
	protected String getTableName()
	{
		return TABLE_NAME;
	}

	@Override
	protected void create() throws SQLException
	{
		PreparedStatement pm = DatabaseConnection.getConnection().prepareStatement("INSERT INTO ".concat(TABLE_NAME).concat(" (users_cart_id, book_id, qty) VALUES(?, ?, ?)"), Statement.RETURN_GENERATED_KEYS);
		pm.setInt(1, _userCartId);
		pm.setInt(2, _bookId);
		pm.setInt(3, _qty);
		
		pm.execute();
		
		setId(DatabaseConnection.lastInsertedId(pm));
	}
	
	@Override
	protected void update() throws SQLException
	{
		PreparedStatement pm = DatabaseConnection.getConnection().prepareStatement("UPDATE ".concat(TABLE_NAME).concat(" SET users_cart_id = ?, book_id = ?, qty = ? WHERE id = ? LIMIT 1;"));
		pm.setInt(1, _userCartId);
		pm.setInt(2, _bookId);
		pm.setInt(3, _qty);
		pm.setInt(4, getId());
		
		pm.execute();
	}
	
	/**
	 *
	 * @return the primary key to the UserCart to which I belong to
	 */
	public int getUserCartId()
	{
		return _userCartId;
	}
	
	/**
	 *
	 * @return the primary key to the book in the UserCart
	 */
	public int getBookId()
	{
		return _bookId;
	}
	
	/**
	 *
	 * @return the quantity of the book object desired
	 */
	public int getQty()
	{
		return _qty;
	}
	
	/**
	 * 
	 * @return the book in the UserCart
	 */
	public Book getBook()
	{
		return _book;
	}
	
	/**
	 * Sets the UserCart to which I belong to
	 * @param userCartId the primary key of the UserCart
	 */
	public void setUserCartId(int userCartId)
	{
		_userCartId = userCartId;
	}
	
	/**
	 * Sets the book id to be desired in a UserCart
	 * @param bookId the primary key of the book
	 */
	public void setBookId(int bookId)
	{
		_bookId = bookId;
	}
	
	/**
	 * Sets the number of requested books
	 * @param qty the number of requested books
	 */
	public void setQty(int qty)
	{
		_qty = qty;
	}
	
	/**
	 * Sets the book to be desired in the UserCart
	 * @param book the book to be desired in the UserCart. Also updates the book_id
	 */
	public void setBook(Book book)
	{
		setBookId(book.getId());
		_book = book;
	}
}
